---
title: "pk72846_view_policy stored procedure documentation"
summary: "Analysis of the pk72846_view_policy Oracle package including business rules and operations."
status: draft
owner: "SAIF Corporation"
last_reviewed: 2026-08-31
source_schema: "WCIS_DBA"
source_name: "pk72846_view_policy"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name pk72846_view_policy"
retrieval_date: "2026-08-31"
---

# pk72846_view_policy

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package (34 members) |
| **Database** | Oracle |
| **Status** | ✅ `found` — retrieved via Legends CLI |
| **Query executed** | `legends oracle query --name pk72846_view_policy` |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicyData`, `clsPolicyPeriodCollection`, `clsAgency`, `clsPolicyAddress`, `clsAffiliatedPolicyCollection`, `clsTeamAssignmentCollection`, `clsMCOCollection`, `clsPolicyMCOCollection` |

## Purpose

`pk72846_view_policy` is the primary policy-view Oracle package for this library. It supplies policy
detail, policy periods, agency, address, coverage-gap, affiliation classification, parent-policy
resolution, and team-assignment retrieval. The package exposes 34 members in total; this library
invokes 11 of them: `getviewdetail`, `getpolicyperiods`, `getagency`, `getgaps`, `getpolicyaddress`,
`getpolicyaddressbylocseqnum`, `getpolicyaffiliatetype`, `getpolicyaffiliatelist`, `getparentpolicy`,
`team_assign_query`, and `getmco` (the last member is not present in the retrieved artifact — see
below).

**Full member inventory (verified from retrieved `codeItems`):** `getcurrentpolpereff`,
`getlocationvwpol`, `getnonexpireddbanames`, `policysearchall`, `getviewheader`, `getviewdetail`,
`team_assign_query`, `getpolicyperiod`, `getpolicyperiods`, `getclasses`, `getaffiliates`,
`getpreferred`, `getexempt`, `getagency`, `getgaps`, `getgapindicator`, `getaffiliations`,
`GetPolicyInfoPageImageSeq`, `getcorresponddata`, `getlocationbyseq`, `getwaivers`,
`getwaiverlocations`, `getwaiverclasses`, `getfederalcoverage`, `getpolicyperiodlist`,
`checkforwaiverclass`, `getpolicyaddress`, `getpolicyaddressbylocseqnum`, `getpolicyteamuser`,
`getpolicyaffiliatetype`, `getpolicyaffiliatelist`, `getparentpolicy`,
`sp73111_get_wcis_std_premium`, `getwrapuplegalname`.

## Procedures / Methods

### getviewdetail

| Field | Value |
| --- | --- |
| **Returns** | `viewpolicydetail_cursor` (IN OUT) |
| **Called by** | `clsPolicyData` policy-detail retrieval (`clsPolicyData.vb:2314`) |

**Description:** Opens the policy view-detail cursor for a policy. Called lazily; a read failure does
not fail the overall policy retrieve (see [BR-VP-026: Detail-read failure does not fail overall retrieve](../business-rules.md#br-vp-026-detail-read-failure-does-not-fail-overall-retrieve)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number |
| `recordset` | IN OUT | `viewpolicydetail_cursor` | — | Result set |

### team_assign_query

| Field | Value |
| --- | --- |
| **Returns** | `team_assign_cursor` (IN OUT) |
| **Called by** | `clsTeamAssignmentCollection.Retrieve` (`clsTeamAssignmentCollection.vb:127-128, 354`) |

**Description:** Returns screen-ordered team assignment rows for a policy plus agent-of-record contact
detail (see rules PR-BVP-038, PR-BVP-039, PR-BVP-040 in [Business Rules](#business-rules) below). The result set includes two
non-team rows coded `Office` and `CST` that the caller must ignore; `clsTeamAssignmentCollection.vb:40-43`
added an `Unknown` enum member on 04/30/2007 (WAYSNI) to absorb them.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number |
| `recordset` | IN OUT | `team_assign_cursor` | — | Result set |

### getpolicyperiods

| Field | Value |
| --- | --- |
| **Returns** | `ref_cursor` (IN OUT) |
| **Called by** | `clsPolicyPeriodCollection.Retrieve` (`clsPolicyPeriodCollection.vb:108-110, 287`) |

**Description:** Returns the top-N (or all) policy periods with plan, group association, and rating
detail, masked per information-page status (see rules PR-BVP-035, PR-BVP-036, PR-BVP-037 in
[Business Rules](#business-rules) below). The retrieved source
contains a sub-select with a literal `WHERE policy_num = 756508` — an observed hard-coded-policy quirk
whose business intent is unknown.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number |
| `atopnpolper` | IN | `NUMBER` | — | Top-N; `0` returns all periods |
| `recordset` | IN OUT | `ref_cursor` | — | Result set |

### getagency

| Field | Value |
| --- | --- |
| **Returns** | `agency_cursor` (IN OUT) |
| **Called by** | `clsAgency.Retrieve` (`clsAgency.vb:278-279, 484`) |

**Description:** Opens the agency cursor for a policy. A missing agency row records a "No data found"
error message on the object rather than throwing
([BR-VP-025](../business-rules.md#br-vp-025-missing-agency-row-records-a-no-data-found-error)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number |
| `recordset` | IN OUT | `agency_cursor` | — | Result set |

### getgaps

| Field | Value |
| --- | --- |
| **Returns** | `gap_cursor` (IN OUT) |
| **Called by** | `clsPolicyData` gap retrieval (`clsPolicyData.vb:1268, 1274`) |

**Description:** Returns computed coverage gaps combined with explicitly recorded gaps (see rules
PR-BVP-030, PR-BVP-031 in [Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number |
| `recordset` | IN OUT | `gap_cursor` | — | Result set |

### getpolicyaddress

| Field | Value |
| --- | --- |
| **Returns** | `ref_cursor` (IN OUT) |
| **Called by** | `clsPolicyAddress.Retrieve` (`clsPolicyAddress.vb:254-259, 572`) |

**Description:** Returns the current address of the requested type, with a claim-to-policy fallback
when no current claim location exists (see rules PR-BVP-032, PR-BVP-033, PR-BVP-034 in
[Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inpolicynum` | IN | `NUMBER` | — | Policy number |
| `inaddresstype` | IN | `VARCHAR2(5)` | — | e.g. `CLM`, `POL` |
| `incontacttype` | IN | `VARCHAR2(5)` | — | Contact type filter |
| `ingetmailaddrwhennotfound` | IN | `VARCHAR2(5)` | — | `Y` enables `CLM`→`POL` fallback |
| `recordset` | IN OUT | `ref_cursor` | — | Result set |

### getpolicyaddressbylocseqnum

| Field | Value |
| --- | --- |
| **Returns** | `ref_cursor` (IN OUT) |
| **Called by** | `clsPolicyAddress` (`clsPolicyAddress.vb:251, 578`) |

**Description:** Returns a single address identified by location sequence number.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inlocationseqnum` | IN | `NUMBER` | — | Location sequence number |
| `recordset` | IN OUT | `ref_cursor` | — | Result set |

### getpolicyaffiliatetype

| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2(50)` (function return value) |
| **Called by** | `clsPolicyData` affiliate-type retrieval (`clsPolicyData.vb:1836-1855`, SQL at `2274`) |

**Description:** Returns the affiliation classification token for a policy (see rules PR-BVP-022 through
PR-BVP-027 in [Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number |
| `in_is_policyholder_user` | IN | `VARCHAR2(5)` | — | `TRUE`/`FALSE` |
| *(return)* | RETURN | `VARCHAR2(50)` | — | Affiliate type token, e.g. `NONAFFILIATED`, `AFFILIATIONPARENT` |

### getpolicyaffiliatelist

| Field | Value |
| --- | --- |
| **Returns** | `ref_cursor` (OUT) |
| **Called by** | `clsAffiliatedPolicyCollection.Retrieve` (`clsAffiliatedPolicyCollection.vb:94-101, 336`) |

**Description:** Returns the affiliate list for a policy, optionally including totals
([BR-VP-011](../business-rules.md#br-vp-011-policy-always-affiliated-with-itself),
[BR-VP-012](../business-rules.md#br-vp-012-affiliated-policy-list-filtered-by-agentagency),
[BR-VP-013](../business-rules.md#br-vp-013-displayaffiliatedpolices-matrix-controls-display)).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number |
| `in_affiliate_type` | IN | `VARCHAR2(25)` | — | Affiliate type token |
| `in_include_affil_totals` | IN | `VARCHAR2` | — | `"TRUE"` / `"FALSE"` |
| `recordset` | OUT | `ref_cursor` | — | Result set |

### getparentpolicy

| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` (function return value) |
| **Called by** | `clsPolicyData` parent-policy retrieval (`clsPolicyData.vb:1803-1823`, SQL at `2280`) |

**Description:** Maps an affiliation classification to the governing parent policy number (see rules
PR-BVP-028, PR-BVP-029 in [Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number |
| `in_is_policyholder_user` | IN | `VARCHAR2(5)` | — | `TRUE`/`FALSE` |
| *(return)* | RETURN | `NUMBER` | — | Parent policy number |

### getmco — member not present in retrieved source

> [!NOTE]
> **`getmco` — member not present in retrieved source.** `clsMCOCollection.vb:231` and
> `clsPolicyMCOCollection.vb:231` both invoke `pk72846_view_policy.getmco`, but a case-insensitive
> search of the full retrieved `pk72846_view_policy` package source returned zero occurrences of
> `getmco`. The package itself is `found`; this specific member is absent from the retrieved artifact.
> This does not change the package's `found` status and does not mean the `clsMCOCollection`/
> `clsPolicyMCOCollection` callers are dead code — both classes have live, reachable call sites. No
> signature, parameters, or logic are documented for `getmco` beyond this caveat and the two caller
> citations below.

| Field | Value |
| --- | --- |
| **Returns** | not documented — member absent from the retrieved package artifact |
| **Called by** | `clsMCOCollection.vb:231`, `clsPolicyMCOCollection.vb:231` |

## Business Rules

Rules extracted from this package's logic (`getmco` contributes none — no rule can be stated for an
absent member):

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BVP-022 | Affiliate type defaults to non-affiliated | no affiliation rule matches | Return `NONAFFILIATED` |
| PR-BVP-023 | Policyholder-user callers get PHP affiliation resolved first | `UPPER(in_is_policyholder_user) = 'TRUE'` AND `policy_relationship` row with `UPPER(affil_typ_cd) = 'PHP'` | `parent_policy_num` match → `AFFILIATIONPARENT`; `mbr_policy_num` match → `AFFILIATIONMEMBER` |
| PR-BVP-024 | Wrap-up and leasing relationships are classified parent vs member by self-reference | `POLICY.policy_relationship_code IN ('WRAP','LEASE')` | `policy_relationship_policy_num = policy_num` → `WRAPUPPARENT` / `LEASINGPARENT`; otherwise `WRAPUPMEMBER` / `LEASINGMEMBER` |
| PR-BVP-025 | Sub-account and out-of-region relationships use the same self-reference test | `policy_relationship_code IN ('SUBAC','OREGN')` | Self-reference → `SUBACPARENT` / `OREGNPARENT`; otherwise `SUBACMEMBER` / `OREGNMEMBER` |
| PR-BVP-026 | Combination-rated policies are classified from the rate account | `POLICY.rate_account = in_policy_num` → parent; `rate_account IS NOT NULL AND rate_account <> in_policy_num` → member | Return `COMBOPARENT` / `COMBOMEMBER` |
| PR-BVP-027 | Minimum-premium combo membership is resolved from the latest combo period | latest `combo_policy_period` with `active_member_flag = 'Y'` | `min_premium_member_flag = 'Y'` → `PREMIUMPARENT`; `NVL(min_premium_member_flag,'N') = 'N'` → `PREMIUMMEMBER` |
| PR-BVP-028 | Parent policy is resolved from the affiliate type | affiliate type from PR-BVP-022..027 | `AFFILIATIONMEMBER` → `policy_relationship.parent_policy_num`; `LEASINGMEMBER`/`WRAPUPMEMBER`/`SUBACMEMBER`/`OREGNMEMBER` → `POLICY.policy_relationship_policy_num`; `COMBOMEMBER` → `POLICY.rate_account`; `PREMIUMMEMBER` → min-premium policy in the same `combo_period_seq_num` |
| PR-BVP-029 | A policy that is not a member is its own parent | no member classification | Return the input policy number |
| PR-BVP-030 | Flat-cancelled and prospect periods never participate in gap calculation | `policy_period_all.policy_period_status_code IN ('FC','PPROS')` | Exclude the period from the computed-gap branch |
| PR-BVP-031 | Coverage gaps combine computed gaps with explicitly recorded gaps | always | UNION computed inter-period gaps with rows from `policy_coverage_gap`; format dates `MM/dd/yyyy`, sort by `yyyyMMdd` descending |
| PR-BVP-032 | Claim address requests fall back to the policy address when no current claim location exists | `ingetmailaddrwhennotfound = 'Y'` AND `inaddresstype = 'CLM'` AND current CLM location count = 0 | Set `v_addr_type := 'POL'` and re-resolve |
| PR-BVP-033 | A missing contact first name displays as `Policyholder` | `contact_first_name IS NULL` | `DECODE` substitutes the literal `Policyholder` |
| PR-BVP-034 | Only currently effective locations are returned, most recent first | always | `NVL(l.LOC_EXP_DATE, SYSDATE+1) > SYSDATE`, `ORDER BY loc_eff_date DESC`, `ROWNUM <= 1` |
| PR-BVP-035 | Zero means "return every period" | `atopnpolper > 0` sets `vretrieveall := atopnpolper`, otherwise it stays `0` | Outer filter `WHERE ROWNUM <= vretrieveall OR 0 = vretrieveall` |
| PR-BVP-036 | Rating values are masked unless the information page has printed | information-page-printed check fails | Apply masking (per in-source comments dated 11/7/11) |
| PR-BVP-037 | Tentatively rated plans are flagged with an asterisk | `tentative_rating_flag = 'Y'` | `DECODE(tentative_rating_flag,'Y','*','')` appended to the plan description |
| PR-BVP-038 | Only screen-ordered team rows are returned for a policy | `p.policy_num = apolicynum AND screen_order > 0` | Return ordered team assignment rows (`ORDER BY 9`) |
| PR-BVP-039 | Agent of record is resolved through the commission authorization package and excludes agent zero | always | `pk72838_comm_authorization.sp72850_get_agent_of_rec(apolicynum)` with `agent.agent_num <> 0` |
| PR-BVP-040 | Agency phone falls back to the location phone when no statement address sequence exists | `agency.statement_addr_loc_seq_num IS NULL` | Use `l2.loc_phone`; otherwise `la.loc_phone` |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this package:

| Operation | Description | Trigger |
| --- | --- | --- |
| Retrieve policy view detail | Opens the policy view-detail cursor for a policy | `clsPolicyData` view-detail retrieval (`clsPolicyData.vb:2314`) |
| Retrieve policy periods | Returns the top-N (or all) policy periods with plan, group association and rating detail, masked per information-page status | `clsPolicyPeriodCollection.Retrieve` (`clsPolicyPeriodCollection.vb:108-110, 287`) |
| Retrieve agency for policy | Opens the agency cursor for a policy | `clsAgency.Retrieve` (`clsAgency.vb:278-279, 484`) |
| Retrieve coverage gaps | Returns computed plus explicitly recorded coverage gaps | `clsPolicyData` gap retrieval (`clsPolicyData.vb:1268, 1274`) |
| Retrieve policy address | Returns the current address of the requested type with claim→policy fallback | `clsPolicyAddress.Retrieve` (`clsPolicyAddress.vb:254-259, 572`) |
| Retrieve address by location sequence | Returns a single address identified by location sequence number | `clsPolicyAddress` (`clsPolicyAddress.vb:251, 578`) |
| Classify policy affiliation | Returns the affiliation classification token for a policy | `clsPolicyData` affiliate-type retrieval (`clsPolicyData.vb:1836-1855`, SQL at `2274`) |
| Resolve parent policy | Maps an affiliation classification to the governing parent policy number | `clsPolicyData` parent-policy retrieval (`clsPolicyData.vb:1803-1823`, SQL at `2280`) |
| List affiliated policies | Returns the affiliate list for a policy, optionally including totals | `clsAffiliatedPolicyCollection.Retrieve` (`clsAffiliatedPolicyCollection.vb:94-101, 336`) |
| Retrieve policy team assignments | Returns screen-ordered team members plus agent-of-record contact detail | `clsTeamAssignmentCollection.Retrieve` (`clsTeamAssignmentCollection.vb:127-128, 354`) |
| Retrieve MCO list | Returns managed-care organisation rows for a policy — `getmco` member absent from the retrieved package artifact | `clsMCOCollection` (`clsMCOCollection.vb:231`), `clsPolicyMCOCollection` (`clsPolicyMCOCollection.vb:231`) |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>Members invoked by SAIF.Business.View.Policy (PL/SQL specification excerpts)</summary>

```sql
PROCEDURE getviewdetail (apolicynum   IN     NUMBER,
                         recordset    IN OUT viewpolicydetail_cursor);

PROCEDURE team_assign_query (apolicynum IN     NUMBER,
                             recordset  IN OUT team_assign_cursor);

PROCEDURE getpolicyperiods (apolicynum  IN     NUMBER,
                            atopnpolper IN     NUMBER,
                            recordset   IN OUT ref_cursor);

PROCEDURE getagency (apolicynum IN     NUMBER,
                     recordset  IN OUT agency_cursor);

PROCEDURE getgaps (apolicynum IN     NUMBER,
                   recordset  IN OUT gap_cursor);

PROCEDURE getpolicyaddress (inpolicynum               IN     NUMBER,
                            inaddresstype             IN     VARCHAR2,
                            incontacttype             IN     VARCHAR2,
                            ingetmailaddrwhennotfound IN     VARCHAR2,
                            recordset                 IN OUT ref_cursor);

PROCEDURE getpolicyaddressbylocseqnum (inlocationseqnum IN     NUMBER,
                                       recordset        IN OUT ref_cursor);

FUNCTION  getpolicyaffiliatetype (in_policy_num           IN NUMBER,
                                  in_is_policyholder_user IN VARCHAR2)
   RETURN VARCHAR2;

PROCEDURE getpolicyaffiliatelist (in_policy_num           IN  NUMBER,
                                  in_affiliate_type       IN  VARCHAR2,
                                  in_include_affil_totals IN  VARCHAR2,
                                  recordset               OUT ref_cursor);

FUNCTION  getparentpolicy (in_policy_num           IN NUMBER,
                           in_is_policyholder_user IN VARCHAR2)
   RETURN NUMBER;
```

</details>

<details>
<summary>Representative rule-bearing excerpts (PL/SQL body)</summary>

```sql
-- getpolicyperiods: top-N handling, 0 means "all"
vretrieveall NUMBER := 0;
...
IF atopnpolper > 0 THEN
   vretrieveall := atopnpolper;
END IF;
...
WHERE ROWNUM <= vretrieveall OR 0 = vretrieveall

-- getpolicyaddress: CLM -> POL fallback
v_addr_type := inaddresstype;
IF ingetmailaddrwhennotfound = 'Y'
   AND inaddresstype = 'CLM'
   AND v_clm_loc_count = 0
THEN
   v_addr_type := 'POL';
END IF;
...
AND NVL (l.loc_exp_date, SYSDATE + 1) > SYSDATE
ORDER BY loc_eff_date DESC
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: `WCIS_DBA.pk72846_view_policy`
- Legends CLI query executed 2026-08-31: `legends oracle query --name pk72846_view_policy`
- Calling code: `SAIF.Business.View.Policy/clsPolicyData.vb`
- Calling code: `SAIF.Business.View.Policy/clsPolicyPeriodCollection.vb`
- Calling code: `SAIF.Business.View.Policy/clsAgency.vb`
- Calling code: `SAIF.Business.View.Policy/clsPolicyAddress.vb`
- Calling code: `SAIF.Business.View.Policy/clsAffiliatedPolicyCollection.vb`
- Calling code: `SAIF.Business.View.Policy/clsTeamAssignmentCollection.vb`
- Calling code: `SAIF.Business.View.Policy/clsMCOCollection.vb:231`
- Calling code: `SAIF.Business.View.Policy/clsPolicyMCOCollection.vb:231`